HW#4 --- last modified January 01 1970 00:00:00..

Solution set.

Due date: Nov 18

Files to be submitted:
  problems.zip
  code.zip

Purpose: To gain practice with concurrency and serializability concepts. To write a few transactions that have commit and rollback's and to do experiments with them.

Specification:

Do problems 19.23 and 19.24 out of the book and submit them in Problems.zip.

For the programming part of the assignment you will write two transactions but rewrite them using three different techniques for talking to an Oracle DB: using JDBC, using SQLJ, and in C using embedded SQL. Your goal will then be to do some experiments with your programs. The JDBC program should make non-trivial use of PreparedStatement. Your transactions will perform their operations on the following table:

CREATE TABLE COUNTER
(
firstDigit INT,
secondDigit INT
);

This table will initially only be populated with one row: (0, 0). Both your transactions update the row. The first transaction reads firstDigit, adds one to it, and updates the row with this new value. If firstDigit is less than 10 after the increment the transaction commits. If firstDigit, however, is a 10 it sets it to 0 and updates. In this case, it then reads secondDigit. If second digit is less than 9 it then increments second digit and update the row again and commit. If secondDigit is a 9 the transaction is rolled back.

The second transaction is like the first except it tries to decrement the two digit number. That is, if firstDigit is bigger than 0 subtract 1 from it, update, and commit. If the firstDigit is a 0 change it to a 9 update the row, and read the secondDigit. If this is bigger than 0 decrement 1 from it update, and commit. Otherwise, rollback.

Given these two transactions you need to :
(1) Design an experiment to determine the average time each transaction takes for each of the three methods: JDBC, SQLJ, and embedded SQL. Make sure to perform enough trials to get a reasonable answer. You should compute this time for where only a single digit changes, in the case both digits are affected and there is a rollback, and in the case both digits are affected and the transaction commits.
(2) Given this you should try to rank the different techniques and comment on whether there is a significant time difference between the methods.
(3) Next write a program which uses your JDBC transactions in two threads. Thread 1 sleeps a random, short (say half a transaction time) interval and then tries to execute the increment transaction. After it finishes the transaction it does the same thing again. Thread 2 is like Thread 1 except it executes the decrement transaction. Make a driver program that allows a 1000 total transactions to be run by these threads. It also keeps track of what it thinks the row value should be. Run an experiment to see if your program matches with what actually is the final row value in the Oracle table. Discuss your results.

Your experiments and results should be in the file code.zip. This file should contain a readme.txt file explaining how using the programs in code.zip to replicate your experiments. It should also contain a file output.txt which contains a print-out of the output of all your experiments together with commentary on your results.

Point Breakdown